/*
 * @(#)SMDTool.java 2016-7-20 下午2:31:52 smd Copyright 2016 Thuisoft, Inc. All
 * rights reserved. THUNISOFT PROPRIETARY/CONFIDENTIAL. Use is subject to
 * license terms.
 */
package com.happut.cmpt;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.happut.cmpt.model.Column;
import com.happut.cmpt.model.Table;
import com.happut.cmpt.model.column.DateColumn;
import com.happut.cmpt.model.column.DateTimeColumn;
import com.happut.cmpt.model.column.IntColumn;
import com.happut.cmpt.model.column.StringColumn;

/**
 * SMDTool
 * @author wfei
 * @time 2016-7-20下午2:31:52
 */
public class SMDTool {
    Map<String, Table> tables = new LinkedHashMap<String, Table>();

    List<String> txt = new ArrayList<String>();

    public static void main(String[] args) {
        String fileName = "f:/卷宗目录SMD-外网.xls";
        File f = new File(fileName);
        if (!f.exists()) {
            return;
        }
        SMDTool smdTool = new SMDTool();
        String destFileName = f.getName();
        int indexExt = f.getName().indexOf(".");
        if (indexExt != -1) {
            destFileName = f.getName().substring(0, indexExt);
        }
        destFileName = f.getParent() + File.separatorChar + destFileName
                + ".sql";
        try {
            smdTool.readExcel(fileName, destFileName);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private void insertToSQLFile(String s) {
        txt.add(s);
    }

    /**
     * @param fileName
     * @param destFileName
     * @author wfei
     * @throws IOException 
     * @throws FileNotFoundException 
     * @time 2016-7-20下午2:34:41
     */
    private void readExcel(String fileName, String destFileName) throws FileNotFoundException, IOException {
        POIFSFileSystem excelFile = new POIFSFileSystem(new FileInputStream(
                fileName));

        // 创建工作簿
        HSSFWorkbook workBook = new HSSFWorkbook(excelFile);

        readTable(workBook);
        readColumn(workBook);

        generateSQL();
        printToFile(destFileName);
    }

    /**
     * 
     * @author wfei
     * @time 2016-7-25下午2:26:15
     */
    private void printToFile(String destFileName) {
        // TODO Auto-generated method stub
        try {
            FileUtils.writeLines(new File(destFileName), "UTF-8", txt);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 
     * @author wfei
     * @time 2016-7-25下午2:18:23
     */
    private void generateSQL() {
        if (true) {

        }
        for (String t : tables.keySet()) {
            StringBuffer primaryKey = new StringBuffer();
            Table table = tables.get(t);
            insertToSQLFile("-- " + table.getNick());
            insertToSQLFile("USE " + table.getNamespace() + ";");
            insertToSQLFile("DROP TABLE IF EXISTS " + table.getName() + ";");
            insertToSQLFile("CREATE TABLE " + table.getName() + "(");
            for (int i = 0; i < table.getColumns().size(); i++) {
                Column c = table.getColumns().get(i);
                insertToSQLFile("    "
                        + c.toColumnSQL(i != table.getColumns().size() - 1
                                || primaryKey.length() > 0));
                if (c.getIsPrimary()) {
                    primaryKey.append(c.getName() + ",");
                }
            }
            if (primaryKey.length() > 0) {
                primaryKey.deleteCharAt(primaryKey.length() - 1);
                insertToSQLFile("    PRIMARY KEY(" + primaryKey.toString()
                        + ")");
            }
            insertToSQLFile(");");
        }
    }

    /**
     * @param workBook
     * @author wfei
     * @time 2016-7-25下午2:01:32
     */
    private void readColumn(HSSFWorkbook workBook) {
        int tabRows = 0;
        HSSFSheet colSheet = workBook.getSheet("COL");
        if (colSheet != null) {
            tabRows = colSheet.getPhysicalNumberOfRows(); // 获得行数
        }

        if (tabRows > 0) {
            // 循环每行,根据SMD文件的格式特点，从第二行开始分析
            for (int i = 2; i <= tabRows; i++) {
                HSSFRow row = colSheet.getRow(i);
                if (row != null) {
                    String id = row.getCell(0) == null ? "" : row.getCell(0)
                            .getStringCellValue().trim();// 变量
                    if (id.equals("----") || id.equals(">>>>")) {
                        continue;
                    }
                    if (id.equals("<<<<")) {
                        return;
                    }

                    String type = row.getCell(6) == null ? "" : row.getCell(6)
                            .getStringCellValue().trim();// 变量类型
                    Column column;
                    if (StringUtils.equals("VC", type)) {
                        column = new StringColumn();
                    } else if (StringUtils.equals("INT", type)) {
                        column = new IntColumn();
                    } else if (StringUtils.equals("DT", type)) {
                        column = new DateTimeColumn();
                    } else if (StringUtils.equals("D", type)) {
                        column = new DateColumn();
                    } else {
                        continue;
                    }

                    String name = row.getCell(1) == null ? "" : row.getCell(1)
                            .getStringCellValue().trim();// 变量类型
                    column.setName(name);

                    String nick = row.getCell(2) == null ? "" : row.getCell(2)
                            .getStringCellValue().trim();// 变量类型
                    column.setNick(nick);

                    double isPrimary = row.getCell(3) == null ? 0 : row
                            .getCell(3).getNumericCellValue();// 变量类型
                    column.setIsPrimary(isPrimary == 1);

                    double length = row.getCell(7) == null ? 0 : row.getCell(7)
                            .getNumericCellValue();// 变量类型
                    column.setLength((int) length);

                    tables.get(id).getColumns().add(column);
                }
            }
        }
    }

    private void readTable(HSSFWorkbook workBook) {
        int tabRows = 0;
        HSSFSheet colSheet = workBook.getSheet("TAB");
        if (colSheet != null) {
            tabRows = colSheet.getPhysicalNumberOfRows(); // 获得行数
        }

        if (tabRows > 0) {
            // 循环每行,根据SMD文件的格式特点，从第二行开始分析
            for (int i = 2; i <= tabRows; i++) {
                HSSFRow row = colSheet.getRow(i);
                if (row != null) {
                    String nick = row.getCell(0) == null ? "" : row.getCell(0)
                            .getStringCellValue().trim();// 变量
                    if (nick.equals("----") || nick.equals(">>>>")) {
                        continue;
                    }
                    if (nick.equals("<<<<")) {
                        return;
                    }

                    Table table = new Table();
                    table.setNick(nick);

                    String name = row.getCell(1) == null ? "" : row.getCell(1)
                            .getStringCellValue().trim();// 变量类型
                    table.setName(name);

                    String namespace = row.getCell(2) == null ? "" : row
                            .getCell(2).getStringCellValue().trim();// 变量类型
                    table.setNamespace(namespace);
                    tables.put(name, table);
                }
            }
        }
    }
}
